package com.dd.service;

import javax.xml.bind.DatatypeConverter;
import java.sql.*;
import java.util.Date;
import java.util.*;

public class JdbcDataCompareUtil {

    // 获取表的主键列名（从数据库元数据获取）
    public static String getPrimaryKeyColumnName(Connection conn, String tableName) throws SQLException {
        DatabaseMetaData metaData = conn.getMetaData();
        ResultSet rs = metaData.getPrimaryKeys(null, null, tableName);
        if (rs.next()) {
            return rs.getString("COLUMN_NAME");
        }
        return null; // 无主键
    }

    // 获取表中所有主键ID
    public static Set<String> getTableIds(Connection conn, String tableName, String primaryKey) throws SQLException {
        Set<String> ids = new HashSet<>();
        String sql = "SELECT " + primaryKey + " FROM " + tableName + " ORDER BY " + primaryKey + " ASC";

        try (Statement stmt = conn.createStatement(); ResultSet rs = stmt.executeQuery(sql)) {
            while (rs.next()) {
                ids.add(rs.getString(primaryKey));
            }
        }
        return ids;
    }

    // 获取表的所有字段名（按表结构顺序）
    public static List<String> getTableColumnNames(Connection conn, String tableName) throws SQLException {
        List<String> columns = new ArrayList<>();
        String sql = "SELECT * FROM " + tableName + " WHERE 1=0";

        try (PreparedStatement pstmt = conn.prepareStatement(sql); ResultSet rs = pstmt.executeQuery()) {
            ResultSetMetaData metaData = rs.getMetaData();
            int columnCount = metaData.getColumnCount();
            for (int i = 1; i <= columnCount; i++) {
                columns.add(metaData.getColumnName(i));
            }
        }
        return columns;
    }

    // 根据ID集合查询源库数据（分批处理大集合）
    public static List<Map<String, Object>> getSourceDataByIds(Connection conn, String tableName, String primaryKey, Set<String> ids, List<String> columnNames) throws SQLException {

        List<Map<String, Object>> dataList = new ArrayList<>();
        if (ids.isEmpty()) {
            return dataList;
        }

        // 分批查询，避免IN子句过长
        int batchSize = 1000; // 每批处理的ID数量
        List<String> idList = new ArrayList<>(ids);

        for (int i = 0; i < idList.size(); i += batchSize) {
            List<String> batchIds = idList.subList(i, Math.min(i + batchSize, idList.size()));
            String idPlaceholders = String.join(",", Collections.nCopies(batchIds.size(), "?"));
            String sql = "SELECT " + String.join(",", columnNames) + " FROM " + tableName + " WHERE " + primaryKey +
                    " IN (" + idPlaceholders + ")" + " ORDER BY " + primaryKey + " ASC";

            try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
                // 设置参数
                for (int j = 0; j < batchIds.size(); j++) {
                    pstmt.setString(j + 1, batchIds.get(j));
                }

                // 执行查询并封装结果
                try (ResultSet rs = pstmt.executeQuery()) {
                    while (rs.next()) {
                        Map<String, Object> data = new HashMap<>();
                        for (String column : columnNames) {
                            data.put(column, rs.getObject(column));
                        }
                        dataList.add(data);
                    }
                }
            }
        }
        return dataList;
    }

    // 生成INSERT语句
    public static String generateInsertSql(String tableName, List<String> columnNames, Map<String, Object> data) {
        StringBuilder columns = new StringBuilder();
        StringBuilder values = new StringBuilder();

        for (String column : columnNames) {
            columns.append("`")
                    .append(column)
                    .append("`,"); // 增加反引号处理特殊字段名
            Object value = data.get(column);

            // 处理不同数据类型的SQL格式化
            if (value == null) {
                values.append("NULL,");
            } else if (value instanceof String || value instanceof Date || value instanceof Timestamp) {
                values.append("'")
                        .append(escapeSql(value.toString()))
                        .append("',");
            } else if (value instanceof Boolean) {
                values.append(((Boolean) value) ? 1 : 0)
                        .append(",");
            } else if (value instanceof java.sql.Blob) {
                // 二进制数据处理（示例：以十六进制格式插入）
                Blob blob = (Blob) value;
                try {
                    byte[] bytes = blob.getBytes(1, (int) blob.length());
                    String hex = DatatypeConverter.printHexBinary(bytes);
                    values.append("0x")
                            .append(hex)
                            .append(",");
                } catch (SQLException e) {
                    values.append("NULL,"); // 出错时设为NULL
                }
            } else {
                values.append(value)
                        .append(",");
            }
        }

        // 移除末尾逗号
        String colsStr = columns.substring(0, columns.length() - 1);
        String valsStr = values.substring(0, values.length() - 1);

        return "INSERT INTO `" + tableName + "` (" + colsStr + ") VALUES (" + valsStr + ")";
    }

    // SQL注入防护：转义单引号
    public static String escapeSql(String value) {
        return value.replace("'", "''");
    }

// 创建等待弹窗


}
